/**
 * @Copyright: Interactive Business Solution Pte Ltd, Singapore
 * @Author: Dolita, 2007-12-24
 * @Version: 071224.001
 * @Change: Li Wen, 2008-03-21
 * Data access object of Customer model
 */
package com.ibsol.database
{
	import com.ibsol.model.Customer;
	
	import flash.data.SQLConnection;
	import flash.data.SQLStatement;
	import flash.events.SQLErrorEvent;
	import flash.events.SQLEvent;
	
	import mx.collections.ArrayCollection;
	import mx.controls.Alert;
	
	public class CustomerDAO
	{
		private var _tourixDBConn:SQLConnection;
		/**
		 * Default constructor of Customer DAO, get database connection
		 */
		public function CustomerDAO()
		{
			_tourixDBConn = DBConnManager.getConnection("tourix");
		}
		/**
		 * Insert data into customer table
		 * customer(id, uid, name, mobile, homeNo, email, gender, isConfirm, comments, offlineOperator, updateTime)
		 * @param: customer:Customer		customer item object which will be populating customer table
		 * 		   resultHandler:Function	result function adapter
		 * 		   faultHandler:Function	fault function adapter
		 * @return: void
		 */
		public function insertCustomer(customer:Customer, resultHandler:Function = null, faultHandler:Function = null):void
		{
			var sqlStr:SQLStatement = new SQLStatement();
			sqlStr.sqlConnection = _tourixDBConn;
			sqlStr.text = "INSERT INTO customer (uid, lastName, mobile, homeNo, email, gender, isConfirm, remarks, offlineOperator, firstName, dob, alertTime, updateTime) " + 
						"VALUES (" + customer.uid + ", '" + customer.lastName + "', '" + customer.mobile + "', '" + customer.homeNo + "', '" + 
						customer.email + "', '" + customer.gender + "', '" + customer.isConfirm + "', '" + customer.remarks + "', 'NEW', '" +
						customer.firstName + "','" + customer.dob + "', '" + customer.alertTime + "', 'T')";
			sqlStr.addEventListener(SQLEvent.RESULT,
				function(event:SQLEvent):void
				{
					customer.id = sqlStr.getResult().lastInsertRowID;
					if(resultHandler != null)
						resultHandler.call(this, customer.id);
				});
			sqlStr.addEventListener(SQLErrorEvent.ERROR, faultHandler == null ? sqlErrorHandler : faultHandler);
			sqlStr.execute();
		}							
		/**
		 * Load customers according to user id (salesman's id)
		 * @param:	uid:int					user id, which customer belongs to
		 * 			resultHandler:Function	result function adapter, return customer item array collection
		 * 			faultHandler:Function	fault function adapter
		 * @return: void
		 */
		public function loadCustomersByUser(uid:int, resultHandler:Function, faultHandler:Function = null):void
		{
			var sqlStr:SQLStatement = new SQLStatement();
			sqlStr.sqlConnection = _tourixDBConn;
			sqlStr.text = "SELECT * FROM customer WHERE uid = " + uid + " AND offlineOperator != 'DEL'";
			sqlStr.addEventListener(SQLEvent.RESULT,
				function (event:SQLEvent):void
				{
					resultHandler.call(this, typeArray(sqlStr.getResult().data));
				});
			sqlStr.addEventListener(SQLErrorEvent.ERROR, faultHandler == null ? sqlErrorHandler : faultHandler);
			sqlStr.execute();
		}
		/**
		 * Updates a customer record in database. For different level of user, there are different options.
		 * customer(id, uid, name, mobile, homeNo, email, gender, isConfirm, comments, offlineOperator, updateTime)
		 * @param:	customer:Customer		customer object which will be updated
		 * 			resultHandler:Function	result handler function
		 * 			faultHandler:Function	fault handler function
		 * @return: void
		 */	
		public function updateCustomer(customer:Customer, resultHandler:Function = null, faultHandler:Function = null):void
		{
			var sqlStr:SQLStatement = new SQLStatement();
			sqlStr.sqlConnection = _tourixDBConn;
			sqlStr.text = "UPDATE customer SET uid=" + customer.uid + ",lastName='" + customer.lastName + "',mobile='" + customer.mobile + 
					"',homeNo='" + customer.homeNo + "',email='" + customer.email + "',gender='" + customer.gender + 
					"',isConfirm=" + customer.isConfirm + ",remarks='" + customer.remarks + 
					"',offlineOperator = 'EDIT',updateTime='T' WHERE id = " + customer.id;
			sqlStr.addEventListener(SQLEvent.RESULT,
				function(event:SQLEvent):void
				{
					if(resultHandler != null)
						resultHandler.call(this);
				});
			sqlStr.addEventListener(SQLErrorEvent.ERROR, faultHandler == null ? sqlErrorHandler : faultHandler);
			sqlStr.execute();
		}
		/**
		 * Delete a customer from database, just update the offlineOperator to 'DEL'
		 */
		public function deleteCustomer(cid:uint, resultHandler:Function = null, faultHandler:Function = null):void
		{
			var sqlStr:SQLStatement = new SQLStatement();
			sqlStr.sqlConnection = _tourixDBConn;
			sqlStr.text = "UPDATE customer SET offlineOperator = 'DEL', updateTime = 'T' WHERE id = " + cid;
			sqlStr.addEventListener(SQLEvent.RESULT,
				function(event:SQLEvent):void
				{
					if(resultHandler != null)
						resultHandler.call(this);
				});
			sqlStr.addEventListener(SQLErrorEvent.ERROR, faultHandler == null ? sqlErrorHandler : faultHandler);
			sqlStr.execute();
		}
		/**
		 * Permanently delete customer from database, whose offlineOperator is set to "DEL". If user wants to delete flight, 
		 * user just updates offlineOperator to "DEL".
		 * @param:	resultHandler:Function		result handler adapter
		 * 			faultHandler:Function		fault handler adapter
		 * @return:	void
		 */
		public function clearDeletedCustomers(resultHandler:Function = null, faultHandler:Function = null):void
		{
			var sqlStr:SQLStatement = new SQLStatement();
			sqlStr.sqlConnection = _tourixDBConn;
			sqlStr.text = "DELETE FROM customer WHERE offlineOperator = 'DEL'";
			sqlStr.addEventListener(SQLEvent.RESULT,
				function (event:SQLEvent):void
				{
					if(resultHandler != null)
						resultHandler.call(this);
				});
			sqlStr.addEventListener(SQLErrorEvent.ERROR, faultHandler == null ? sqlErrorHandler : faultHandler);
			sqlStr.execute();
		}
		/**
		 * Sets offline operator to "", this function called after synchronization with server
		 * @param:	resultHandler:Function		result handler adapter
		 * 			faultHandler:Function		fault handler adapter
		 * @return: void
		 */
		public function resetOfflineOperator(resultHandler:Function = null, faultHandler:Function = null):void
		{
			var sqlStr:SQLStatement = new SQLStatement();
			sqlStr.sqlConnection = _tourixDBConn;
			sqlStr.text = "UPDATE customer SET offlineOperator=''";
			sqlStr.addEventListener(SQLEvent.RESULT,
				function (event:SQLEvent):void
				{
					if(resultHandler != null)
						resultHandler.call(this);
				});
			sqlStr.addEventListener(SQLErrorEvent.ERROR, faultHandler == null ? sqlErrorHandler : faultHandler);
			sqlStr.execute();
		}
		/**
		 * Converts database object to Customer class
		 * @param:	o:Object	database return object
		 * @return:	Customer		Customer object after conversion
		 */
		private function typeObject(o:Object):Customer
		{ 
			var cu:Customer = new Customer();
			cu.id = o.id;
			cu.uid = o.uid;
			cu.lastName = o.lastName;
			cu.mobile = o.mobile;
			cu.homeNo = o.homeNo;
			cu.email = o.email;
			cu.gender = o.gender;
			cu.isConfirm = o.isConfirm;
			cu.remarks = o.remarks;
			cu.offlineOperator = o.offlineOperator;
			cu.updateTime = o.updateTime;
			cu.firstName = o.firstName;
			cu.dob = o.dob;
			cu.alertTime = o.alertTime;
			return cu;
		}
		/**
		 * Converts array object to Flight array collection
		 * @param: a:Array array of objects returned by SQL
		 * @return: ArrayCollection of Flight after conversion from Array
		 */
		private function typeArray(a:Array):ArrayCollection
		{
			if (a==null) return null;
			var ac:ArrayCollection = new ArrayCollection();
			for (var i:int; i<a.length; i++)
			{
				ac.addItem(typeObject(a[i]));
			}
			return ac;
		}
		/**
		 * Default SQL error handler, if above functions' fault handlers are null, this error handler is applied
		 * @param: event:SQLError	SQL Error event will be handled if there is an error
		 * @return: void
		 */
		private function sqlErrorHandler(event:SQLErrorEvent):void
		{
			mx.controls.Alert.show(event.text, "Error");
			trace(event.text);
		}
	}
}